package cn.easybuy.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.easybuy.dao.BaseDao;
import cn.easybuy.dao.ProductCategoryDao;
import cn.easybuy.entity.ProductCategory;

public class ProductCategoryDaoImpl extends BaseDao implements ProductCategoryDao{

	@Override
	public List<ProductCategory> getProductCategoriesByParentId(int parentId) {
		String sql="select * from easybuy_product_category where parentId=?";
		ResultSet rs=this.exxcuteQuery(sql, parentId);
		List<ProductCategory> list=new ArrayList<ProductCategory>();
		try {
			while(rs.next()){
				ProductCategory pCategory=new ProductCategory();
				pCategory.setId(rs.getInt(1));
				pCategory.setName(rs.getString(2));
				pCategory.setParentId(rs.getInt(3));
				pCategory.setType(rs.getInt(4));
				pCategory.setIconClass(rs.getString(5));
				list.add(pCategory);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return list;
	} 

	@Override
	public List<ProductCategory> getProductCategoriesByType(int type) {
		String sql="select * from easybuy_product_category where type=?";
		ResultSet rs=this.exxcuteQuery(sql, type);
		List<ProductCategory> list=new ArrayList<ProductCategory>();
		try {
			while(rs.next()){
				ProductCategory pCategory=new ProductCategory();
				pCategory.setId(rs.getInt(1));
				pCategory.setName(rs.getString(2));
				pCategory.setParentId(rs.getInt(3));
				pCategory.setType(rs.getInt(4));
				pCategory.setIconClass(rs.getString(5));
				list.add(pCategory);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return list;
	}

	@Override
	public String getParantNameById(int id) {
		String sql="SELECT `name` FROM easybuy_product_category WHERE id=(SELECT parentId FROM easybuy_product_category WHERE id=?)";
		ResultSet rs=this.exxcuteQuery(sql, id);
		String name=null;
		try {
			while(rs.next()){
				name=rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		
		return name;
	}

	@Override
	public String getGrandParantNameById(int id) {
		String sql="SELECT `name` FROM easybuy_product_category WHERE id=(SELECT parentId FROM easybuy_product_category WHERE id=(SELECT parentId FROM easybuy_product_category WHERE id=?))";
		ResultSet rs=this.exxcuteQuery(sql, id);
		String name=null;
		try {
			while(rs.next()){
				name=rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		
		return name;
	}

	@Override
	public int getProductIdByName(String name) {
		String sql="SELECT `id` FROM easybuy_product_category WHERE `name`=?";
		ResultSet rs=this.exxcuteQuery(sql, name);
		int id=0;
		try {
			while(rs.next()){
				id=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return id;
	}

	@Override
	public List<ProductCategory> getProductCategoriesAll(int pageNo,int pageSize) {
		String sql="SELECT pc1.`id`,pc1.`name`,pc2.`name` name2,pc1.`type`,pc1.`iconClass` FROM easybuy_product_category pc1 LEFT JOIN easybuy_product_category pc2 ON pc1.`parentId`=pc2.`id` order by `type` limit ?,?";
		ResultSet rs=this.exxcuteQuery(sql,(pageNo-1)*pageSize,pageSize);
		List<ProductCategory> list=new ArrayList<ProductCategory>();
		try {
			while(rs.next()){
				ProductCategory pCategory=new ProductCategory();
				pCategory.setId(rs.getInt(1));
				pCategory.setName(rs.getString(2));
				pCategory.setParName(rs.getString(3));
				pCategory.setType(rs.getInt(4));
				pCategory.setIconClass(rs.getString(5));
				list.add(pCategory);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return list;
	}

	@Override
	public int totalCount() {
		String sql="select count(1) from easybuy_product_category";
		int totalCount=0;
		ResultSet rs=this.exxcuteQuery(sql);
		try {
			while (rs.next()) {
				totalCount=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(connection, pstmt, rs);
		}
		return totalCount;
	}


	

}
